if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InjectPSDemogFeed]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InjectPSDemogFeed]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


/***************************************************************************
* Name:			InjectPSDemogFeed
* Purpose:		Using table Stg_IE_DemogFeed, inject new and changed agreement
*				demographic data.
*
* SCRAPED DATA		Coded
* -------------		-----
* Cost Center		
* Sponsor
* Fund
*
*
* AGREEMENT Tables Maintained
* Name									Coded	Operations					Done	
* -------------							-----	-------------------------	----
* AgrAgreement							I/U		Insert/Update				Y
* AgrBudgetContainer					I		Insert/Update				Y
* AgrBudgetContainerView				I		Insert/Update				Y
* AgrBudget								I		Insert/Update				Y
* SvcEntity_FwkDomainOrganization_Map			Insert/Update				Y
* AgrActivity									Insert/Update				Y
* SvcEntity								I		Insert						Y
* FwkResource							I		Insert						Y
* AnnAnnotationContainer				I		Insert						Y
* SvcEntity_AdmFacility_map						Insert/Update (Primary)		Y
* SvcEntity_AdmAdministrator_map				N/A
* SvcEntity_AdmStaffedPerson_map				Insert/Update (PI)			Y
*
* FUND Tables Maintained
* Name									Coded	Operations					Done	
* -------------							-----	-------------------------	----
* AgrFund										Insert/Update				Y
* SvcEntity										Insert						Y
* AgrFund_AgrAgreement_Map						Insert/Update				Y
* AgrCostCenter_AgrFund_Map						Insert/Update				Y
* AgrCostCenter									Insert						Y
* SvcEntity_AdmSponsor_map						Insert/Update				Y
* AdmSponsor									Insert/Update				Y
* 
* 
* 
* PARAMETERS
* Name				Description					
* -------------		-----------------------------------------------------------
* n/a
*
* ERRORS USED		Description
* -------------		-----------------------------------------------------
* n/a
*
* RETURN VALUE
* Value    			Description					
* --------------	------------------------------------------------------
* @_exitStatus		An exit status of either true or false
***************************************************************************/
CREATE PROCEDURE [dbo].[InjectPSDemogFeed]
AS
BEGIN

	SET NOCOUNT ON

	Begin Try

	begin transaction InjectPSDemogFeed

/*
  **************************************
  * S C R A P E D  D A T A             *
  **************************************
*/
	--NEW COST CENTERS
	SET IDENTITY_INSERT AgrCostCenter ON

	Insert into AgrCostCenter(
		Id
		,Number
		,DateCreated
		,AgrCostCenterSystemTypeId
	)
	Select s.AgrCostCenterId,s.Cost_center,getdate(), 
			case s.business_Unit when '1200' then 1 else 2 end as AgrCostCenterSystemTypeId
	FROM	Stg_IE_DemogFeed s
	WHERE	s.IsNewCostCenter = 1 and s.IsValid = 1

	SET IDENTITY_INSERT AgrCostCenter OFF

	--NEW SPONSORS
	SET IDENTITY_INSERT AdmSponsor ON

	Insert into AdmSponsor(
		Id,[Name],ShortName,Description,AdmSponsorTypeId,DateCreated,RexSponsorId)
	Select distinct s.AdmSponsorId,s.sponsor_name,left(s.sponsor_name,25),s.sponsor_name,IsNull(st.id,6)
		,getdate(), null
	FROM	Stg_IE_DemogFeed s
	left outer join AdmSponsorType st on st.PSCode = s.sponsor_code_id
	WHERE	s.IsNewSponsor = 1 and s.IsValid = 1

	SET IDENTITY_INSERT AdmSponsor OFF

/*
  **************************************
  * N E W  A G R E E M E N T S         *
  **************************************
*/
	-- NEW FwkResources
	SET IDENTITY_INSERT FwkResource ON

	INSERT	FwkResource (Id, FwkResourceTypeId)
	SELECT	DISTINCT s.FwkResourceId, 1
	FROM	Stg_IE_DemogFeed s
	WHERE	s.IsNewAgreement = 1 and s.IsValid = 1 and IsAgreementMaintained = 1

	SET IDENTITY_INSERT FwkResource OFF

	-- NEW SvcEntity
	SET IDENTITY_INSERT SvcEntity ON

	INSERT	SvcEntity (Id, SvcEntityTypeId, FwkResourceId)
	SELECT	DISTINCT s.SvcEntityId, 1, s.FwkResourceId
	FROM	Stg_IE_DemogFeed s
	WHERE	s.IsNewAgreement = 1 and s.IsValid = 1 and IsAgreementMaintained = 1

	SET IDENTITY_INSERT SvcEntity OFF

	-- NEW AgrBudgetContainer
	SET IDENTITY_INSERT AgrBudgetContainer ON

	INSERT			AgrBudgetContainer (Id, Name, Description, IndirectCostBaseId, AgrBudgetTemplateTypeId)
	SELECT DISTINCT s.AgrBudgetContainerId, 
					CAST(s.AgrAgreementID AS varchar) + ' ' + s.Folder_Number, 
					'Agreement Budget for Rex agreement Id ' + CAST(s.AgrAgreementID AS varchar), 
					coalesce((select p.finICBaseTypeId from FinPSICBaseType p 
								 where p.[id] = isNull(s.ic_base_int,0))
							 ,1/* Default to MTDC if nothing else */),
					1	-- hard-coded value for budget template type Id
	FROM			Stg_IE_DemogFeed s
	WHERE	s.IsNewAgreement = 1 and s.IsValid = 1 and s.IsCurrentFund = 1 and IsAgreementMaintained = 1

	SET IDENTITY_INSERT AgrBudgetContainer OFF

	--NEW AgrBudgetContainerView for awarded
	SET IDENTITY_INSERT AgrBudgetContainerView ON

	INSERT INTO AgrBudgetContainerView (Id,AgrBudgetContainerId, AgrBudgetViewTypeId, TargetPatientNumber
									, PerCapitaValue, InflationRate, CurrentGrantYear)
	SELECT s.AgrBudgetContainerViewId,s.AgrBudgetContainerId
					,3/*Awarded Budget View Type*/,0,0,0,1 
	FROM Stg_IE_DemogFeed s
	WHERE	s.IsValid = 1 and s.IsCurrentFund = 1 and IsAgreementMaintained = 1
			and (s.IsNewAgreement = 1 
				or (not exists(select 1 from AgrBudgetContainerView 
								where id = s.AgrBudgetContainerViewId)
					and s.AgrBudgetContainerViewId is not null))

	SET IDENTITY_INSERT AgrBudgetContainerView OFF

	-- NEW AnnAnnotationContainer
	SET IDENTITY_INSERT AnnAnnotationContainer ON

	INSERT	AnnAnnotationContainer (Id, SbsEntityTypeId)
	SELECT	distinct s.AnnAnnotationContainerId, 21
	FROM	Stg_IE_DemogFeed s
	WHERE	s.IsNewAgreement = 1 and s.IsValid = 1 and IsAgreementMaintained = 1

	SET IDENTITY_INSERT AnnAnnotationContainer OFF

/*
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	I N S E R T  A G R A G R E E M E N T
	Insert the agreement that is flagged as a new agreement based on its current fund.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
	SET IDENTITY_INSERT AgrAgreement ON

	INSERT	AgrAgreement
			(
				Id,
				CurrentId,
				PIPersonId,
				AgrTransactionTypeId,
				AgrAgreementTypeId,
				AgrAgreementStatusTypeId,
				OverallStatus,
				FolderNumber,
				ParentFolderNumber,
				ShortTitle,
				Title,
				DocumentNumber,
				RexOwned,
				IsCurrent,
				IsAmendment,
				AgrBudgetContainerId,
				LegacyIdentifier,
				LegacySystemName,
				ReceivedDate,
				ProjectStartDate,
				ProjectEndDate,
				AgrNumberSchemeTypeId,
				Resubmission,
				Response,
				RFAID,
				RFATitle,
				InsertedDate,
				SvcEntityId,
				FwkResourceId,
				AnnAnnotationContainerId,
				DateLastModified
			)
	SELECT distinct	AgrAgreementId,
				AgrAgreementId,
				PI_AdmPersonId,
				AgrTransactionTypeId,
				AgrAgreementTypeId,
				AgrAgreementStatusTypeId,
				'Fully Executed/Awarded - Activated' as OverallStatus,
				substring(rtrim(ltrim(Folder_Number)),1,12),
				'' as ParentFolderNumber,
				description,
				long_description,
				1 as DocumentNumber,
				0 as RexOwned,
				1 as IsCurrent,
				0 as IsAmendment,
				AgrBudgetContainerId,
				0 as LegacyIdentifier,
				'' as LegacySystemName,
				dtEffective, --ReceivedDate,
				proj_start_date,
				proj_end_date,
				1 as MultipleGrantNumbering,
				0 as Resubmission,
				0 as  Response,
				0 as RFAID,
				'' as RFATitle,
				GetDate(),
				SvcEntityId,
				FwkResourceId,
				AnnAnnotationContainerId,
				getdate()
	FROM	Stg_IE_DemogFeed s
	WHERE	s.IsNewAgreement = 1 and s.IsValid = 1 and s.IsCurrentFund = 1 and IsAgreementMaintained = 1
	SET IDENTITY_INSERT AgrAgreement OFF

/*
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	I N S E R T  S V C E N T I T Y  F A C I L I T Y  M A P
	Insert the facility map for an agreement with no prior facility map row.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
	INSERT	SvcEntity_AdmFacility_Map ( SvcEntityId, AdmFacilityId, AdmFacilityRelationshipTypeId, PercentActivity, DateCreated)
	SELECT	s.SvcEntityId, s.AdmFacilityId, 1 /* Primary AdmFacilityRelationshipTypeId */, 100 /*nPercentActivity*/, getdate()
	FROM	Stg_IE_DemogFeed s
	WHERE	s.svcEntityFacilityMapId is null and s.IsValid = 1 and s.IsCurrentFund = 1  and IsAgreementMaintained = 1
			and IsNull(s.AdmFacilityId,0)<>0
/*
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	I N S E R T  S V C E N T I T Y  O R G A N I Z A T I O N  M A P
	Insert the organization map for an agreement that has no organization map.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
	Insert Into svcentity_fwkDomainOrganization_map(
		 SvcEntityId
		,fwkDomainOrganizationId
		,fwkOrganizationRelationshipTypeId
		,DateCreated
	)
	Select s.svcEntityId, s.fwkDomainOrganizationId, 1 /* primary org*/ , getdate()
	From Stg_IE_DemogFeed s where s.IsValid = 1 and s.IsCurrentFund = 1  and IsAgreementMaintained = 1
		AND not exists(Select 1 from svcentity_fwkDomainOrganization_map om 
						where om.svcEntityId = s.svcEntityId and om.fwkOrganizationRelationshipTypeId = 1)
/*
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	I N S E R T  A C T I V I T Y
	Insert the AgrActivity table for a new agreement.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
	Insert Into AgrActivity(
		 AgrAgreementId
		,AgrActivityTypeid
		,ActivityType
		,ActivitySubType
		,ActivitySubSubType
		,SequenceNo
	)
	Select s.AgrAgreementId, s.AgrActivityTypeid,'','','',0
	From Stg_IE_DemogFeed s where s.IsValid = 1  and s.IsCurrentFund = 1  and IsAgreementMaintained = 1
		AND not exists(Select 1 from AgrActivity ac 
						where ac.AgrAgreementId = s.AgrAgreementId)
/*
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	I N S E R T  S V C E N T I T Y  S T A F F E D  P I  P E R S O N  
	Insert the PI into the SvcEntity_StaffedPerson_Map table for a new agreement.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
	Insert Into SvcEntity_AdmStaffedPerson_Map(
		 SvcEntityId
		,AdmPersonId
		,AdmStaffedPersonTypeId
		,DateCreated
	)
	Select s.svcEntityId, s.PI_AdmPersonId, 1 /* PI*/ , getdate()
	From Stg_IE_DemogFeed s where s.IsValid = 1 and s.IsCurrentFund = 1 and IsAgreementMaintained = 1
		AND not exists(Select 1 from SvcEntity_AdmStaffedPerson_Map spm 
						where spm.svcEntityId = s.svcEntityId and AdmStaffedPersonTypeId = 1)
/*
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	I N S E R T  S V C E N T I T Y  A D M S P O N S O R 
	Insert the Sponsor into the SvcEntity_AdmSponsor_Map table for a new agreement.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
	Insert Into SvcEntity_AdmSponsor_Map(
		 SvcEntityId
		,AdmSponsorId
		,AdmSponsorRelationTypeId
		,SponsorNumber
		,DateCreated
		,CFDANumber
	)
	Select s.svcEntityId, s.AdmSponsorId, 1 as AdmSponsorRelationTypeId,
		sponsor_agree_id_format, getdate() as DateCreated ,CFDA_number
	From Stg_IE_DemogFeed s where s.IsValid = 1 and s.IsCurrentFund = 1 and s.IsAgreementMaintained = 1
		AND not exists(Select 1 from SvcEntity_AdmSponsor_Map spm 
						where spm.svcEntityId = s.svcEntityId and AdmSponsorRelationTypeId = 1)

	-- NEW FUND FwkResources
	SET IDENTITY_INSERT FwkResource ON

	INSERT	FwkResource (Id, FwkResourceTypeId)
	SELECT	DISTINCT s.FundFwkResourceId, 4
	FROM	Stg_IE_DemogFeed s
	WHERE	s.IsNewFund = 1 and s.IsValid = 1  and AgrAgreementId is not null

	SET IDENTITY_INSERT FwkResource OFF

/*
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	I N S E R T  S V C E N T I T Y  F O R  N E W  F U N D S 
	Insert the service entity for new funds.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	SET IDENTITY_INSERT SvcEntity ON

	INSERT	SvcEntity (Id, SvcEntityTypeId, FwkResourceId)
	SELECT	s.AgrFundSvcEntityId, 3, s.FundFwkResourceId
	FROM	Stg_IE_DemogFeed s
	WHERE	s.IsNewFund = 1 and s.IsValid = 1  and AgrAgreementId is not null

	SET IDENTITY_INSERT SvcEntity OFF
*/
/*
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	I N S E R T  A G R F U N D  
	Insert the fund into the AgrFund table for a new fund.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
	SET IDENTITY_INSERT AgrFund ON

	INSERT		AgrFund (
					Id, FwkResourceId,SvcEntityId, FinAccountCycleTypeId, FundNumber, AgrFundStatusTypeId, 
					AgrFundAwardTypeId, AgrFundOverheadClassificationId, CFDA, 
					DocumentLetter, FundShortTitle, FundTitle, BusinessUnitId, RexOwned, 
					ActivationDate, EffectiveDate, AgrFundTypeId, AgrFundSubTypeId, ManagerId
					)
	SELECT			s.AgrFundId, s.FundFwkResourceId, s.SvcEntityId, s.FinAccountCycleTypeId, s.project_grant, s.AgrFundStatusTypeId, 
					s.AgrFundAwardTypeId, s.AgrFundOverheadClassificationId, s.cfda_number, 
					s.doc_letter, left(s.Description,50), left(s.long_Description,128), s.BusinessUnitId, 
					case IsNull(ltrim(rtrim(s.folder_number)),'') when '' then 0 else 1 end as RexOwned, 
					s.ActivationDate, s.Effective_Date, 1, s.AgrFundSubTypeId, s.PI_AdmPersonId
	FROM		Stg_IE_DemogFeed s
	WHERE		s.IsNewFund = 1 and s.IsValid = 1 and AgrAgreementId is not null

	SET IDENTITY_INSERT AgrFund OFF

/*
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	I N S E R T  A G R F U N D _ A G R A G R E E M E N T _ M A P  
	Insert the fund into the AgrFund_AgrAgreement_Map table for a new agreement.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
	SET IDENTITY_INSERT AgrFund_AgrAgreement_Map ON

	INSERT	AgrFund_AgrAgreement_Map (
				Id, AgrFundId, AgrAgreementId, SvcEntityId, FinAccountCycleTypeId, FundNumber, 
				AgrFundStatusTypeId, AgrFundAwardTypeId, AgrFundOverheadClassificationId, 
				CFDA, DocumentLetter, FundShortTitle, FundTitle, EffectiveDate, 
				BusinessUnitId, RexOwned, ActivationDate, AgrFundTypeId, AgrFundSubTypeId, ManagerId
				)
	SELECT		AgrFundAgreementMapId, AgrFundId, AgrAgreementId, SvcEntityId, FinAccountCycleTypeId, project_grant, 
				AgrFundStatusTypeId, AgrFundAwardTypeId, AgrFundOverheadClassificationId, 
				cfda_number, doc_letter, left(Description,50), left(Long_Description,128), Effective_Date, 
				BusinessUnitId, case IsNull(ltrim(rtrim(folder_number)),'') when '' then 0 else 1 end
					as RexOwned, ActivationDate, 1, AgrFundSubTypeId, PI_AdmPersonId
	FROM	Stg_IE_DemogFeed 
	WHERE	IsValid = 1 and AgrFundAgreementMapId is not null and AgrAgreementId is not null
			and not exists(Select 1 from AgrFund_AgrAgreement_Map fm 
							where fm.id = Stg_IE_DemogFeed.AgrFundAgreementMapId)

	SET IDENTITY_INSERT AgrFund_AgrAgreement_Map OFF

/*
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	I N S E R T  A G R B U D G E T  
	Insert the budget if this is a maintained agreement and there was no container view
	or this is a new agreement.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
	SET IDENTITY_INSERT AgrBudget ON

	INSERT INTO AgrBudget (Id,AgrBudgetContainerViewId, AgrBudgetTypeId, StartDate, EndDate
							,[Year], IsAwarded, AgrFundAgreeMapId, IsAdjustment)
	SELECT DISTINCT s.AgrBudgetId,s.AgrBudgetContainerViewId,1/*Annual Budget Type*/
					,coalesce(s.dtBudgetStart,s.dtprojectstart,'1/1/1900')
					,coalesce(s.dtBudgetEnd,s.dtprojectend,'12/31/2099')
					,1,1,s.AgrFundAgreementMapId,0 
	FROM Stg_IE_DemogFeed s
	WHERE	s.IsValid = 1 and s.agrBudgetId is not null  and IsAgreementMaintained = 1 
			and not exists(select 1 from AgrBudget where id = s.AgrBudgetId)

	SET IDENTITY_INSERT AgrBudget OFF


/*
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	I N S E R T  A G R B U D G E T  I T E M
	--NEW AgrBudgetItem for awarded for only new agreements that are maintained by this feed.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
	INSERT INTO AgrBudgetItem (AgrBudgetId, AgrContainerAccountTemplateMapId, ItemValue)
	SELECT s.AgrBudgetId,tm.Id,0 
	FROM Stg_IE_DemogFeed s
	inner join AgrBudgetItem_containerAccount_Template_map tm on tm.AgrContainerTemplateId = 1
					and tm.AgrAccountTemplateId = (select min(tm1.AgrAccountTemplateId) from AgrBudgetItem_containerAccount_Template_map tm1
													where tm1.AgrContainerTemplateId = 1)
	WHERE	s.IsValid = 1 
			and s.agrBudgetId is not null  and IsAgreementMaintained = 1
			and not exists(select 1 from AgrBudgetItem
								where AgrBudgetId = s.AgrBudgetId 
									and agrContainerAccountTemplateMapId = tm.Id)

/*
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	I N S E R T  A G R B U D G E T  I C  R A T E
	Insert the budget if this is a maintained agreement and there was no container view
	or this is a new agreement.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
INSERT INTO AgrBudgetICRate (AgrBudgetId,Rate,NumberOfMonths,SequenceNo,EffectiveDate)
	SELECT DISTINCT s.AgrBudgetId,s.indirect_cost_rate,12,1,null
	FROM Stg_IE_DemogFeed s
	WHERE	s.IsValid = 1
			and s.agrBudgetId is not null  and IsAgreementMaintained = 1
			and not exists(select 1 from AgrBudgetICRate where AgrBudgetId = s.AgrBudgetId)
/*
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	I N S E R T  A G R C O S T C E N T E R _ A G R F U N D _ M A P  
	Insert the fund into the AgrCostCenter_AgrFund_Map table for a new agreement.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
	INSERT	AgrCostCenter_AgrFund_Map
				(AgrFundAgreementMapId, AgrCostCenterId, DateCreated, AgrCostCenterStatusTypeId)
	SELECT	AgrFundAgreementMapId, AgrCostCenterId, GetDate(), AgrCostCenterStatusTypeId
	FROM	Stg_IE_DemogFeed 
	WHERE	IsValid = 1 and AgrFundAgreementMapId is not null and AgrAgreementId is not null
			and AgrCostCenterId is not null
			and not exists(Select 1 from AgrCostCenter_AgrFund_Map cm 
							where cm.AgrFundAgreementMapId = Stg_IE_DemogFeed.AgrFundAgreementMapId)


/*
  **************************************
  * C H A N G E D  A G R E E M E N T S *
  **************************************
*/

/*
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	U P D A T E   A G R A G R E E M E N T
	Change the data on the TOP DOC of the main agreement.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
	Update	a
	Set
		PIPersonId = s.PI_AdmPersonId,
		AgrTransactionTypeId = s.AgrTransactionTypeId,
		AgrAgreementTypeId = s.AgrAgreementTypeId,
		AgrAgreementStatusTypeId = s.AgrAgreementStatusTypeId,
		OverallStatus = 'Fully Executed/Awarded - Activated',
		ShortTitle = s.description,
		Title = s.long_description,
		IsCurrent = 1,
		IsAmendment = 0,
		ProjectStartDate = s.dtProjectStart,
		ProjectEndDate = s.dtProjectEnd,
		DateLastModified = GetDate()
	FROM	Stg_IE_DemogFeed s
	INNER JOIN AgrAgreement a on a.Id = s.AgrAgreementId 
	where s.IsNewAgreement = 0 and s.IsValid = 1 and s.IsChanged = 1 and s.IsCurrentFund = 1
		and s.IsAgreementMaintained = 1
/*
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	U P D A T E   I C  B A S I S  O N  B U D G E T  C O N T A I N E R  F O R  A G R E E M E N T
	The only reason to ever update a budget container is if the IC basis code changes.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
	UPDATE bc 
	SET IndirectCostBaseId = case s.ic_base_int	
								when 0 Then 6 -- Lump Sum
								when 1 then 3 -- TDC (0% & up)
								when 2 then 5 -- S & W
								when 3 then 1 -- MTDC
								when 6 then 1 -- MTDC
								when 8 then 2 -- TADC (T32 Training Only)
								when 9 then 7 -- Inactivate O.H.
								when 10 then 1 -- MTDC
								when 11 then 1 -- MTDC
								when 12 then 1 -- MTDC
								when 13 then 2 -- TADC (T32 Training Only)
								when 14 then 5 -- S & W
								when 15 then 3 -- TDC (0% & up)
								else 1 -- MTDC
							end
	FROM Stg_IE_DemogFeed s INNER JOIN AgrBudgetContainer bc on bc.id = s.AgrBudgetContainerId
	where s.IsNewAgreement = 0 and s.IsValid = 1 and s.IsChanged = 1 and s.IsCurrentFund = 1
		--and s.IsAgreementMaintained = 1

/*
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	U P D A T E  S V C E N T I T Y  O R G A N I Z A T I O N  M A P
	Update the svcentity organization map if there is a change.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
	Update sfm
	Set fwkDomainOrganizationId = s.fwkDomainOrganizationId
	From Stg_IE_DemogFeed s INNER JOIN svcentity_fwkDomainOrganization_map sfm 
						  on s.svcentityId = sfm.svcentityId and sfm.fwkorganizationRelationshipTypeId = 1 -- primary
	where s.IsNewAgreement = 0 and s.IsValid = 1 and s.IsCurrentFund = 1 and s.IsChanged = 1 
			and s.Orig_fwkDomainOrganizationId <> s.fwkDomainOrganizationId
			and s.IsAgreementMaintained = 1

/*
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	U P D A T E  A G R  A C T I V I T Y
	Update the AgrActivity table if there is a change.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
	Update ac
	Set AgrActivityTypeId = s.AgrActivityTypeId
	From Stg_IE_DemogFeed s INNER JOIN AgrActivity ac on s.AgrAgreementId = ac.AgrAgreementId
	where s.IsNewAgreement = 0 and s.IsValid = 1 and s.IsCurrentFund = 1 and s.IsChanged = 1 
		and s.Orig_AgrActivityTypeId <> s.AgrActivityTypeId
		and s.IsAgreementMaintained = 1

/*
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	U P D A T E  S V C E N T I T Y  F A C I L I T Y  M A P
	Update the facility map table if any changes.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
	Update sfm
	Set AdmFacilityId = s.AdmFacilityId
		,DateCreated = getdate()
	From Stg_IE_DemogFeed s INNER JOIN SvcEntity_AdmFacility_Map sfm 
						  on s.svcentityId = sfm.svcentityId and sfm.AdmFacilityRelationshipTypeId = 1 -- primary
	where s.IsNewAgreement = 0 and s.IsValid = 1 and s.IsChanged = 1 and s.IsCurrentFund = 1
		 and s.Orig_AdmFacilityId <> s.AdmFacilityId and IsNull(s.admFacilityId,0)<>0
		and s.IsAgreementMaintained = 1

/*
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	U P D A T E  T H E  P I  I N  S T A F F E D  P E R S O N  M A P  
	Update the PI in the staffed person map table if any changes in PI.  We don't get any other
	staff in the PS demog feed.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
	Update sfm
	Set AdmPersonId = s.PI_AdmPersonId
		,DateCreated = getdate()
	From Stg_IE_DemogFeed s INNER JOIN SvcEntity_AdmStaffedPerson_Map sfm 
						  on s.svcentityId = sfm.svcentityId and sfm.AdmStaffedPersonTypeId = 1 -- PI
	where s.IsNewAgreement = 0 and s.IsValid = 1 and s.IsChanged = 1 and s.IsCurrentFund = 1 
		and s.Orig_PI_AdmPersonId <> s.PI_AdmPersonId
		and s.IsAgreementMaintained = 1

/*
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	U P D A T E  A D M S P O N S O R 
	Update the Sponsor for an agreement.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	Update spsr
		Set [Name] = s.sponsor_name
			,ShortName =Left(s.sponsor_name,25)
			,Description = s.sponsor_name
			,AdmSponsorTypeId = IsNull(st.id,6)
			,DateCreated = getdate()
	FROM	Stg_IE_DemogFeed s inner join admSponsor spsr on spsr.Id = s.AdmSponsorId
	left outer join AdmSponsorType st on st.pscode = s.sponsor_code_id
	WHERE	s.IsNewSponsor = 0 and s.IsValid = 1 and s.IsChanged = 1 and s.IsCurrentFund = 1
			and s.IsAgreementMaintained = 1 and s.sponsor_name <> 'SUNDRY'
*/

/*
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	U P D A T E  S V C E N T I T Y  A D M S P O N S O R 
	Update the Sponsor into the SvcEntity_AdmSponsor_Map table for a new agreement.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
	Update sm 
	Set  
		 AdmSponsorId = s.AdmSponsorId
		,AdmSponsorRelationTypeId = 1
		,SponsorNumber = s.sponsor_agree_id_format
		,DateCreated = getdate()
		,CFDANumber = s.CFDA_number
	From Stg_IE_DemogFeed s 
	inner join SvcEntity_AdmSponsor_Map sm on sm.SvcEntityId = s.SvcEntityId
				and AdmSponsorRelationTypeId = 1
	where s.IsNewAgreement = 0 and s.IsChanged = 1 and s.IsValid = 1 and s.IsCurrentFund = 1
			and s.IsAgreementMaintained = 1

/*
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	U P D A T E  A G R F U N D  
	Update the fund into the AgrFund table for a new agreement.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
	UPDATE f
	SET
		FinAccountCycleTypeId				= s.FinAccountCycleTypeId
		,AgrFundStatusTypeId				= s.AgrFundStatusTypeId
		,AgrFundAwardTypeId					= s.AgrFundAwardTypeId
		,AgrFundOverheadClassificationId	= s.AgrFundOverheadClassificationId
		,CFDA								= s.cfda_number
		,DocumentLetter						= s.doc_letter
		,FundShortTitle						= left(s.Description,50)
		,FundTitle							= left(s.long_Description,128)
		,BusinessUnitId						= s.BusinessUnitId
		,RexOwned							= case IsNull(ltrim(rtrim(s.folder_number)),'') when '' then 0 else 1 end
		,EffectiveDate						= s.Effective_Date
		,ActivationDate						= s.ActivationDate
		,AgrFundTypeId						= 1
		,AgrFundSubTypeId					= s.AgrFundSubTypeId
		,ManagerId							= s.PI_AdmPersonId
	FROM		Stg_IE_DemogFeed s
	INNER JOIN	AgrFund f on f.Id = s.AgrFundId
	WHERE		s.IsNewFund = 0 and s.IsValid = 1 

/*
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	U P D A T E  A G R F U N D _ A G R A G R E E M E N T _ M A P  
	Update the fund into the AgrFund_AgrAgreement_Map table for an agreement.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
	UPDATE	f 
	SET			
		AgrFundId							= s.AgrFundId
		, AgrAgreementId					= s.AgrAgreementId
		, SvcEntityId						= s.SvcEntityId
		, FinAccountCycleTypeId				= s.FinAccountCycleTypeId
		, FundNumber						= s.project_grant
		, AgrFundStatusTypeId				= s.AgrFundStatusTypeId
		, AgrFundAwardTypeId				= s.AgrFundAwardTypeId
		, AgrFundOverheadClassificationId	= s.AgrFundOverheadClassificationId
		, CFDA								= s.cfda_number
		, DocumentLetter					= s.doc_letter
		, FundShortTitle					= left(s.Description,50)
		, FundTitle							= left(s.Long_Description,128)
		, EffectiveDate						= s.Effective_Date
		, ActivationDate					= s.ActivationDate
		, BusinessUnitId					= s.BusinessUnitId
		, RexOwned							= case IsNull(ltrim(rtrim(s.folder_number)),'') when '' then 0 else 1 end
		, AgrFundTypeId						= 1
		, AgrFundSubTypeId					= s.AgrFundSubTypeId
		, ManagerId							= s.PI_AdmPersonId
	FROM		Stg_IE_DemogFeed s
	INNER JOIN	AgrFund_AgrAgreement_Map f on f.Id = s.AgrFundAgreementMapId
	WHERE		s.IsNewFund = 0 and s.IsValid = 1 and s.agragreementid is not null

/*
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	U P D A T E  A G R B U D G E T  
	Update the budget if this is a maintained agreement.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
	UPDATE b
	SET
		startDate = coalesce(s.dtBudgetStart,s.dtprojectstart,'1/1/1900')
		,EndDate = coalesce(s.dtBudgetEnd,s.dtprojectend,'12/31/2099')
		,AgrFundAgreeMapId = s.AgrFundAgreementMapId
	FROM Stg_IE_DemogFeed s
	INNER JOIN AgrBudget b on b.id = s.agrBudgetId
	WHERE	s.IsValid = 1  and s.AgrFundAgreementMapId is not null and s.IsAgreementMaintained = 1

/*
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	U P D A T E  A G R B U D G E T  I C  R A T E
	Update the budget IC Rate if this is a maintained agreement.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
	UPDATE b
	SET Rate = s.indirect_cost_rate
	FROM Stg_IE_DemogFeed s
	INNER JOIN AgrBudgetICRate b on b.agrBudgetid = s.agrBudgetId
	WHERE	s.IsValid = 1 and s.IsAgreementMaintained = 1
/*
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	U P D A T E  A G R C O S T C E N T E R _ A G R F U N D _ M A P  
	Update the cost center and status into the AgrCostCenter_AgrFund_Map table for an agreement.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
*/
	UPDATE cm
	SET	 AgrCostCenterId = s.AgrCostCenterId
		,AgrCostCenterStatusTypeId = s.AgrCostCenterStatusTypeId
		,DateCreated = getdate()
	FROM 
		Stg_IE_DemogFeed s 
	INNER JOIN 
		AgrCostCenter_AgrFund_Map cm 
	ON
		cm.AgrFundAgreementMapId = s.AgrFundAgreementMapId
		and cm.AgrCostCenterId = s.Orig_AgrCostCenterId
	WHERE
		s.IsNewCostCenter = 0 and s.IsValid = 1 and s.AgrFundAgreementMapId is not null
		and s.AgrCostCenterId is not null and s.agragreementid is not null

	commit transaction InjectPSDemogFeed

	End Try

    BEGIN CATCH
    DECLARE 
        @ErrorMessage    NVARCHAR(4000),
        @ErrorNumber     INT,
        @ErrorSeverity   INT,
        @ErrorState      INT,
        @ErrorLine       INT,
        @ErrorProcedure  NVARCHAR(200);

    -- Assign variables to error-handling functions that 
    -- capture information for RAISERROR.
    SELECT 
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

    -- Building the message string that will contain original
    -- error information.
    SELECT @ErrorMessage = 
        N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
            'Message: '+ ERROR_MESSAGE();

	IF XACT_STATE() <> 0 Rollback transaction InjectPSDemogFeed

    -- Raise an error: msg_str parameter of RAISERROR will contain
    -- the original error information.
    RAISERROR 
        (
        @ErrorMessage, 
        @ErrorSeverity, 
        1,               
        @ErrorNumber,    -- parameter: original error number.
        @ErrorSeverity,  -- parameter: original error severity.
        @ErrorState,     -- parameter: original error state.
        @ErrorProcedure, -- parameter: original error procedure name.
        @ErrorLine       -- parameter: original error line number.
        );
		return 99
    END CATCH;
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

